package com.amado.easyui2.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.amado.easyui2.dao.CoachCarDao;
import com.amado.easyui2.domain.CoachCarEntity;
import com.amado.easyui2.domain.User;
import com.amado.easyui2.util.DBConnection;
import com.amado.easyui2.vo.Page;

public class CoachCarDaoImpl implements CoachCarDao {

	@Override
	public void add(CoachCarEntity lcn) {
		Connection conn = JDBC.getConn();
		String sql = "insert into t_coach_car(bh_abbreviate,learn_card_number,old_learn_card_number,car_coloc,carrriage_number,engine_number,training_model,paraphrase_way,index_number_number,body_color_car,automobile_brand,car_type,create_time,update_time,create_name,update_name)values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		PreparedStatement ps = null;
		try {
//		int i=1;
			ps = conn.prepareStatement(sql);
			ps.setString(1, lcn.getBhAbbreviate());
			ps.setString(2, lcn.getLearnCardNumber());
			ps.setString(3, lcn.getOldLearnCardNumber());
			ps.setString(4, lcn.getCarColoc());
			ps.setString(5, lcn.getCarrriageNumber());
			ps.setString(6, lcn.getEngineNumber());
			ps.setString(7, lcn.getTrainingModel());
			ps.setString(8, lcn.getParaphraseWay());
			ps.setString(9, lcn.getIndexNumberNumber());
			ps.setString(10, lcn.getBodyColorCar());
			ps.setString(11, lcn.getAutomobileBrand());
			ps.setString(12, lcn.getCarType());
			ps.setTimestamp(13, new java.sql.Timestamp(System.currentTimeMillis()));
			ps.setTimestamp(14, new java.sql.Timestamp(System.currentTimeMillis()));
			ps.setString(15, lcn.getCreateName());
			ps.setString(16, lcn.getUpdateName());
			
//		ps.setDate(14, coachName.getRegisterDay());
//		ps.setDate(15, coachName.getComeintoserviceDay());
//		ps.setDate(16, coachName.getYearCarefulTime());
//		ps.setDate(16, coachName.getYearCarefulState());
			ps.execute();
		} catch (SQLException e) {

			e.printStackTrace();
		}
		JDBC.close(null, ps, conn);

	}

	@Override
	public List<CoachCarEntity> listAll() {
		Connection conn = JDBC.getConn();
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "SELECT * FROM t_coach_car";
		List<CoachCarEntity> coach = new ArrayList<CoachCarEntity>();
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				int i = 1;
				Long id = rs.getLong(i++);
				String bhAbbreviate = rs.getString(i++);
				
				String learnCardNumber = rs.getString(i++);
				String oldLearnCardNumber = rs.getString(i++);
				String carColoc = rs.getString(i++);
				String carrriageNumber = rs.getString(i++);
				String engineNumber = rs.getString(i++);
				String trainingModel = rs.getString(i++);
				String paraphraseWay = rs.getString(i++);
				String indexNumberNumber = rs.getString(i++);
				String bodyColorCar = rs.getString(i++);
				String automobileBrand = rs.getString(i++);
				String carType = rs.getString(i++);
				Date registerDay = rs.getDate(i++);
				Date comeintoserviceDay = rs.getDate(i++);
				Date yearCarefulTime = rs.getDate(i++);
				String yearCarefulState = rs.getString(i++);
				String deleteLogo = rs.getString(i++);
				Date createTime=rs.getTimestamp(i++);
				Date updateTime=rs.getTimestamp(i++);
				String createName=rs.getString(i++);
				String updateName=rs.getString(i++);
				coach.add(new CoachCarEntity(id, bhAbbreviate,deleteLogo,learnCardNumber, oldLearnCardNumber,
						carColoc, carrriageNumber, engineNumber,trainingModel, paraphraseWay, indexNumberNumber,
						bodyColorCar, automobileBrand, carType, registerDay, comeintoserviceDay, yearCarefulTime,
						yearCarefulState,createTime,updateTime,createName,updateName));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBC.close(rs, ps, conn);
		}
		return coach;
	}

	@Override
	public void delete(List<Long> ids) {
		if(ids==null || ids.isEmpty()) {
			return;
		}
		Connection conn = JDBC.getConn();
		StringBuilder sql =new StringBuilder("update t_coach_car set delete_logo=? where id in (");
		for(int i=0;i<ids.size();i++) {
			if(i==0) {
				sql.append("?");
			}else {
				sql.append(",?");
			}
		}
		sql.append(")");
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql.toString());
			ps.setString(1, "Y");
			for(int i=0;i<ids.size();i++) {
				ps.setLong(i+2, ids.get(i));
			}
			ps.executeUpdate();
		} catch (SQLException e) {
			
			e.printStackTrace();
		}
		JDBC.close(null, ps, conn);
	}

	@Override
	public int update(CoachCarEntity entity) {
		Connection conn = JDBC.getConn();
		String sql = "update t_coach_car set bh_abbreviate=?,learn_card_number=?,old_learn_card_number=?,car_coloc=?,carrriage_number=?,engine_number=?,training_model=?,paraphrase_way=?,index_number_number=?,body_color_car=?,automobile_brand=?,car_type=?,create_time=?,update_time=?,create_name=?,update_name=? where id=?";
		PreparedStatement ps = null;
		try {
			int i = 1;
			ps = conn.prepareStatement(sql);
			ps.setString(i++, entity.getBhAbbreviate());
			ps.setString(i++, entity.getLearnCardNumber());
			ps.setString(i++, entity.getOldLearnCardNumber());
			ps.setString(i++, entity.getCarColoc());
			ps.setString(i++, entity.getCarrriageNumber());
			ps.setString(i++, entity.getEngineNumber());
			ps.setString(i++, entity.getTrainingModel());
			ps.setString(i++, entity.getParaphraseWay());
			ps.setString(i++, entity.getIndexNumberNumber());
			ps.setString(i++, entity.getBodyColorCar());
			ps.setString(i++, entity.getAutomobileBrand());
			ps.setString(i++, entity.getCarType());
			ps.setTimestamp(i++, new java.sql.Timestamp(System.currentTimeMillis()));
			ps.setTimestamp(i++, new java.sql.Timestamp(System.currentTimeMillis()));
			ps.setString(i++, entity.getCreateName());
			ps.setString(i++, entity.getUpdateName());
//			ps.setLong(i++, id.getRegisterDay());
			ps.setLong(i++, entity.getID());
			return ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		JDBC.close(null, ps, conn);
		return 0;
	}

	@Override
	public List<CoachCarEntity> listAll(String tm, String lcn) {
		Connection conn = JDBC.getConn();
		String sql = "select * from t_coach_car where delete_logo='N'";
		PreparedStatement ps = null;
		ResultSet rs = null;
		if (lcn != null && lcn.trim() != "") {
			sql = sql + " and learn_card_number like ?";
		}
		if (tm != null && tm.trim() != "") {
			sql = sql + " and training_model =?";
		}
		System.out.println("sql" + sql);
		List<CoachCarEntity> list = new ArrayList<CoachCarEntity>();
		try {
			ps = conn.prepareStatement(sql);
			int j = 1;
			if (lcn != null && lcn.trim() != "") {
				ps.setString(j++, lcn);
			}
			if (tm != null && tm.trim() != "") {
				ps.setString(j++, "%" + tm + "%");
			}
			rs = ps.executeQuery();
			while (rs.next()) {
				int i = 1;
				Long id = rs.getLong(i++);
				String bhAbbreviate = rs.getString(i++);
				String deleteLogo = rs.getString(i++);
				String learnCardNumber = rs.getString(i++);
				String oldLearnCardNumber = rs.getString(i++);
				String carColoc = rs.getString(i++);
				String carrriageNumber = rs.getString(i++);
				String engineNumber = rs.getString(i++);
				String trainingModel = rs.getString(i++);
				String paraphraseWay = rs.getString(i++);
				String indexNumberNumber = rs.getString(i++);
				String bodyColorCar = rs.getString(i++);
				String automobileBrand = rs.getString(i++);
				String carType = rs.getString(i++);
				Date registerDay = rs.getTimestamp(i++);
				Date comeintoserviceDay = rs.getTimestamp(i++);
				Date yearCarefulTime = rs.getTimestamp(i++);
				String yearCarefulState = rs.getString(i++);
				Date createTime=rs.getTimestamp(i++);
				Date updateTime=rs.getTimestamp(i++);
				String createName=rs.getString(i++);
				String updateName=rs.getString(i++);
				list.add(new CoachCarEntity(id, bhAbbreviate, deleteLogo, learnCardNumber, oldLearnCardNumber, carColoc,
						carrriageNumber, engineNumber, trainingModel, paraphraseWay, indexNumberNumber, bodyColorCar,
						automobileBrand, carType, registerDay, comeintoserviceDay, yearCarefulTime, yearCarefulState,createTime,updateTime,createName,updateName));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		JDBC.close(rs, ps, conn);
		return list;
	}

	@Override
	public int listAllCount(String tm, String lcn) {
		Connection conn = DBConnection.getConn();
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "SELECT count(id) FROM t_coach_car where delete_logo='N'";
		if (lcn != null && lcn.trim() != "") {
			sql = sql + " and learn_card_number like ?";
		}
		if (tm != null && tm.trim() != "") {
			sql = sql + " and training_model like ?";
		}
		
		System.out.println("sql" + sql);
		List<CoachCarEntity> list = new ArrayList<CoachCarEntity>();
		try {
			ps = conn.prepareStatement(sql);
			int j = 1;
			if (tm != null && tm.trim() != "") {
				ps.setString(j++, tm);
			}
			if (lcn != null && lcn.trim() != "") {
				ps.setString(j++, "%" + lcn + "%");
			}
			
			rs = ps.executeQuery();
			while (rs.next()) {
				return rs.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBConnection.close(rs, ps, conn);
		}
		return 0;
	}

	@Override
	public List<CoachCarEntity> listAll(String tm, String lcn, Page page) {
		Connection conn = DBConnection.getConn();
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "SELECT * FROM t_coach_car where delete_logo='N'";
		if (tm != null && tm.trim() != "") {
			sql = sql + " and training_model =?";
		}
		if (lcn != null && lcn.trim() != "") {
			sql = sql + " and learn_card_number like ?";
		}
		sql = sql + "limit ?,?";
		System.out.println("sql" + sql);
		List<CoachCarEntity> list = new ArrayList<CoachCarEntity>();
		try {
			ps = conn.prepareStatement(sql);
			int j = 1;
			if (tm != null && tm.trim() != "") {
				ps.setString(j++, tm);
			}
			if (lcn != null && lcn.trim() != "") {
				ps.setString(j++, "%" + lcn + "%");
			}
			ps.setInt(j++, (page.getCurrentPage() - 1) * page.getRowSize());
			ps.setInt(j++, page.getRowSize());
			rs = ps.executeQuery();
			while (rs.next()) {
				int i = 1;
				Long id = rs.getLong(i++);
				String bhAbbreviate = rs.getString(i++);
				String learnCardNumber = rs.getString(i++);
				String oldLearnCardNumber = rs.getString(i++);
				String carColoc = rs.getString(i++);
				String carrriageNumber = rs.getString(i++);
				String engineNumber = rs.getString(i++);
				String trainingModel = rs.getString(i++);
				String paraphraseWay = rs.getString(i++);
				String indexNumberNumber = rs.getString(i++);
				String bodyColorCar = rs.getString(i++);
				String automobileBrand = rs.getString(i++);
				String carType = rs.getString(i++);
				Date registerDay = rs.getTimestamp(i++);
				Date comeintoserviceDay = rs.getTimestamp(i++);
				Date yearCarefulTime = rs.getTimestamp(i++);
				String yearCarefulState = rs.getString(i++);
				String deleteLogo = rs.getString(i++);
				Date createTime=rs.getTimestamp(i++);
				Date updateTime=rs.getTimestamp(i++);
				String createName=rs.getString(i++);
				String updateName=rs.getString(i++);
				list.add(new CoachCarEntity(id, bhAbbreviate, deleteLogo, learnCardNumber, oldLearnCardNumber, carColoc,
						carrriageNumber, engineNumber, trainingModel, paraphraseWay, indexNumberNumber, bodyColorCar,
						automobileBrand, carType, registerDay, comeintoserviceDay, yearCarefulTime, yearCarefulState,createTime,updateTime,createName,updateName));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBConnection.close(rs, ps, conn);
		}
		return list;
	}

	@Override
	public List<CoachCarEntity> login(CoachCarEntity lcn) {
		Connection conn = DBConnection.getConn();
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "SELECT * FROM t_coach_car where coach_name=? and coach_number=? ";
		List<CoachCarEntity> list = new ArrayList<CoachCarEntity>();
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				int i = 1;
				String learnCardNumber = rs.getString(i++);
				String trainingModel = rs.getString(i++);

				list.add(new CoachCarEntity(learnCardNumber, trainingModel));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}

}
